package starkuz.lg.dao.impl.sites;

import starkuz.lg.dao.ifaces.sites.CtrSiteDao;
import starkuz.lg.model.SiteStatuses;
import starkuz.lg.model.sites.CtrSite;
import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Repository
public class CtrSiteDaoImpl implements CtrSiteDao {

    private static final String INSERT_CTR_SITE =
            "insert into ctr_sites (ownerId, idTemplate, titleTag, keywordTag, descripTag, mainHeader, briefDef," +
                    " companyName, mySite, dopText," +
                    "statCode, goToUrl, btnTxt, blackBox, status, bgImg, dateMade, dateLastChange, marketType) " +
                    "values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    private static final String UPDATE_CTR_SITE =
            "update ctr_sites set ownerId = ?, idTemplate = ?, titleTag = ?, keywordTag = ?, descripTag = ?, mainHeader = ?, briefDef = ?, " +
                    " companyName = ?, mySite = ?, dopText = ?, statCode = ?, goToUrl = ?, btnTxt = ?, blackBox = ?, bgImg = ?, " +
                    "dateLastChange = ?, marketType = ?  where id = ?";
    private static final String SELECT_ALL_ACTUAL_CTR_SITES_BY_USER_ID =
            "select * from ctr_sites where ownerId = ? and not status = 'deleted'";
    private static final String SELECT_CTR_SITE_BY_ID = "select * from ctr_sites where id = ?";
    private static final String UPDATE_CTR_SITE_TO_DELETED =
            "update ctr_sites set status = 'deleted' where id = ?";
    private static final String SELECT_ALL_CTR_SITES_BY_USER_ID =
            "select * from ctr_sites where ownerId = ?";

    @Autowired
    private BasicDataSource ds;

    public void setDs(BasicDataSource ds) {
        this.ds = ds;
    }

    @Override
    public List<CtrSite> getAllCtrSites() {
        return null;
    }

    @Override
    public CtrSite findCtrSiteById(Integer id) {
        CtrSite ctrSite = new CtrSite();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_CTR_SITE_BY_ID);
            psql.setInt(1, id);
            ctrSite = resultSetToCtrSite(psql.executeQuery());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return ctrSite;
    }

    @Override
    public void addCtrSite(CtrSite ctrSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(INSERT_CTR_SITE);
            psql.setInt(1, ctrSite.getOwnerId());
            psql.setInt(2, ctrSite.getIdTemplate());
            psql.setString(3, ctrSite.getTitleTag());
            psql.setString(4, ctrSite.getKeywordTag());
            psql.setString(5, ctrSite.getDescripTag());
            psql.setString(6, ctrSite.getMainHeader());
            psql.setString(7, ctrSite.getBriefDef());
            psql.setString(8, ctrSite.getCompanyName());
            psql.setString(9, ctrSite.getMySite());
            psql.setString(10, ctrSite.getDopText());
            psql.setString(11, ctrSite.getStatCode());
            psql.setString(12, ctrSite.getGoToUrl());
            psql.setString(13, ctrSite.getBtnTxt());
            psql.setInt(14, ctrSite.getBlackBox());
            psql.setString(15, SiteStatuses.SITE_STATUS_ACTIVE); //при создании сайт активен
            psql.setString(16, ctrSite.getBgImg());
            psql.setDate(17, new Date(new java.util.Date().getTime())); //дата в момент создания сайта
            psql.setDate(18, new Date(new java.util.Date().getTime()));
            psql.setString(19, ctrSite.getMarketType());
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public void deleteCtrSiteById(Integer ctrSiteId) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con  = ds.getConnection();
            psql = con.prepareStatement(UPDATE_CTR_SITE_TO_DELETED);
            psql.setInt(1, ctrSiteId);
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    @Override
    public void updateCtrSite(CtrSite ctrSite) {
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(UPDATE_CTR_SITE);
            psql.setInt(1, ctrSite.getOwnerId());
            psql.setInt(2, ctrSite.getIdTemplate());
            psql.setString(3, ctrSite.getTitleTag());
            psql.setString(4, ctrSite.getKeywordTag());
            psql.setString(5, ctrSite.getDescripTag());
            psql.setString(6, ctrSite.getMainHeader());
            psql.setString(7, ctrSite.getBriefDef());
            psql.setString(8, ctrSite.getCompanyName());
            psql.setString(9, ctrSite.getMySite());
            psql.setString(10, ctrSite.getDopText());
            psql.setString(11, ctrSite.getStatCode());
            psql.setString(12, ctrSite.getGoToUrl());
            psql.setString(13, ctrSite.getBtnTxt());
            psql.setInt(14, ctrSite.getBlackBox());
            psql.setString(15, ctrSite.getBgImg());
            psql.setDate(16, new Date(new java.util.Date().getTime()));
            psql.setString(17, ctrSite.getMarketType());
            psql.setInt(18, ctrSite.getId());
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public List<CtrSite> getAllCtrSitesForUser(Integer userId) {
        List<CtrSite> sites = new ArrayList<CtrSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_CTR_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToCtrSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<CtrSite> getAllActualCtrSitesForUser(Integer userId) {
        List<CtrSite> sites = new ArrayList<CtrSite>();
        Connection con = null;
        PreparedStatement psql = null;
        try {
            con = ds.getConnection();
            psql = con.prepareStatement(SELECT_ALL_ACTUAL_CTR_SITES_BY_USER_ID);
            psql.setInt(1, userId);
            sites = resultSetToCtrSitesList(psql.executeQuery(), sites);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                psql.close();
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return sites;
    }

    @Override
    public List<CtrSite> getAllCtrSitesForUserByStatus(Integer userId, String status) {
        return null;  //To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    public List<CtrSite> getAllSitesByStatus(String status) {
        return null;  //To change body of implemented methods use File | Settings | File Templates.
    }

    @Override
    public void setStatusBySiteId(Integer siteId, String status) {
        //To change body of implemented methods use File | Settings | File Templates.
    }

    private List<CtrSite> resultSetToCtrSitesList(ResultSet rs, List<CtrSite> sites) throws SQLException {
        while (rs.next()) {
            CtrSite ctrSite = new CtrSite();
            ctrSite.setId(rs.getInt("id"));
            ctrSite.setOwnerId(rs.getInt("ownerId"));
            ctrSite.setIdTemplate(rs.getInt("idTemplate"));
            ctrSite.setTitleTag(rs.getString("titleTag"));
            ctrSite.setKeywordTag(rs.getString("keywordTag"));
            ctrSite.setDescripTag(rs.getString("descripTag"));
            ctrSite.setMainHeader(rs.getString("mainHeader"));
            ctrSite.setBriefDef(rs.getString("briefDef"));
            ctrSite.setCompanyName(rs.getString("companyName"));
            ctrSite.setMySite(rs.getString("mySite"));
            ctrSite.setDopText(rs.getString("dopText"));
            ctrSite.setStatCode(rs.getString("statCode"));
            ctrSite.setGoToUrl(rs.getString("goToUrl"));
            ctrSite.setGoToUrl(rs.getString("btnTxt"));
            ctrSite.setMarketType(rs.getString("marketType"));
            ctrSite.setStatus(rs.getString("status"));
            ctrSite.setBgImg(rs.getString("bgImg"));
            ctrSite.setBlackBox(rs.getInt("blackBox"));
            ctrSite.setDateMade(rs.getDate("dateMade"));
            ctrSite.setDateLastChange(rs.getDate("dateLastChange"));

            sites.add(ctrSite);
        }
        return sites;
    }
    private CtrSite resultSetToCtrSite(ResultSet rs) throws SQLException {
        CtrSite ctrSite = new CtrSite();
        while (rs.next()) {
            ctrSite.setId(rs.getInt("id"));
            ctrSite.setOwnerId(rs.getInt("ownerId"));
            ctrSite.setIdTemplate(rs.getInt("idTemplate"));
            ctrSite.setTitleTag(rs.getString("titleTag"));
            ctrSite.setKeywordTag(rs.getString("keywordTag"));
            ctrSite.setDescripTag(rs.getString("descripTag"));
            ctrSite.setMainHeader(rs.getString("mainHeader"));
            ctrSite.setBriefDef(rs.getString("briefDef"));
            ctrSite.setCompanyName(rs.getString("companyName"));
            ctrSite.setMySite(rs.getString("mySite"));
            ctrSite.setDopText(rs.getString("dopText"));
            ctrSite.setStatCode(rs.getString("statCode"));
            ctrSite.setGoToUrl(rs.getString("goToUrl"));
            ctrSite.setBtnTxt(rs.getString("btnTxt"));
            ctrSite.setMarketType(rs.getString("marketType"));
            ctrSite.setStatus(rs.getString("status"));
            ctrSite.setBgImg(rs.getString("bgImg"));
            ctrSite.setBlackBox(rs.getInt("blackBox"));
            ctrSite.setDateMade(rs.getDate("dateMade"));
            ctrSite.setDateLastChange(rs.getDate("dateLastChange"));

        }
        return ctrSite;
    }
}
